// SQLite相关功能
let currentPage = 1;
const recordsPerPage = 10;
let tableData = [];
let totalRecords = 0;
let currentTableName = '';
let currentDatabaseName = '';

// 加载所有数据库
function loadAllDatabases() {
  console.log('loadAllDatabases');
  $.ajax({
    url: '/api/sqlite/databases',
    type: 'GET',
    dataType: 'json',
    success: function(data) {
      console.log('loadAllDatabases', data);
      const databaseTree = $('#database-tree');
      databaseTree.empty();
      
      if (data && data.length > 0) {
        data.forEach(db => {
          const dbId = db.name.replace('.db', '');
          const dbElement = $(
            `<div class="database-item mb-2">
              <div class="database-header bg-secondary text-white p-2 rounded cursor-pointer" onclick="toggleDatabase('${dbId}')">
                <i class="fa fa-database mr-2"></i> ${db.name}
              </div>
              <div id="${dbId}-tables" class="mt-1 pl-4 d-none"></div>
            </div>`
          );
          databaseTree.append(dbElement);
        });
      } else {
        databaseTree.append('<div class="text-center text-muted p-3">未找到数据库</div>');
      }
    },
    error: function() {
      console.error('加载数据库列表失败');
    }
  });
}

// 切换数据库展开/收起
function toggleDatabase(databaseId) {
  const tablesElement = document.getElementById(`${databaseId}-tables`);
  
  if (tablesElement.classList.contains('d-none')) {
    // 展开时加载表列表
    loadTables(databaseId, tablesElement);
  }
  
  tablesElement.classList.toggle('d-none');
}

// 加载数据库中的表列表
function loadTables(databaseId, tablesElement) {
  // 清空现有内容并显示加载中
  $(tablesElement).empty().append('<div class="text-center text-muted p-2"><i class="fa fa-spinner fa-spin"></i> 加载中...</div>');
  
  $.ajax({
    url: '/api/sqlite/tables',
    type: 'GET',
    data: { db_name: databaseId + '.db' },
    dataType: 'json',
    success: function(data) {
      $(tablesElement).empty();
      if (data && data.length > 0) {
        data.forEach(table => {
          const tableItem = $(
            `<div class="table-item p-2 hover:bg-light rounded cursor-pointer" onclick="loadTableData('${databaseId}', '${table.name}')">
              <i class="fa fa-table mr-2"></i> ${table.name}
            </div>`
          );
          $(tablesElement).append(tableItem);
        });
      } else {
        $(tablesElement).append('<div class="text-center text-muted p-2">该数据库中没有表</div>');
      }
    },
    error: function() {
      $(tablesElement).html('<div class="text-center text-danger p-2">加载表列表失败</div>');
    }
  });
}

// 加载表数据
function loadTableData(databaseId, tableName) {
  // 移除之前的活动状态
  document.querySelectorAll('.table-item').forEach(item => {
    item.classList.remove('active');
  });
  
  // 添加当前活动状态
  const currentTableItem = event.currentTarget;
  currentTableItem.classList.add('active');
  
  currentDatabaseName = databaseId;
  currentTableName = tableName;
  currentPage = 1;
  // 更新SQL控制台当前库名
  const sqlDbEl = document.getElementById('sql-current-db');
  if (sqlDbEl) sqlDbEl.textContent = `${databaseId}.db`;
  
  // 更新标题
  document.getElementById('table-title').innerHTML = `<i class="fa fa-table mr-2"></i>${databaseId}.db - ${tableName}`;
  
  // 显示加载状态
  const tableContainer = document.getElementById('table-container');
  tableContainer.innerHTML = '<div class="text-center py-8"><i class="fa fa-spinner fa-spin fa-3x text-muted mb-3"></i><p class="text-muted">加载数据中...</p></div>';
  document.getElementById('pagination-container').classList.add('d-none');
  
  // 调用API获取表数据
  fetchTableData(databaseId, tableName, currentPage);
}

// 从API获取表数据
function fetchTableData(databaseId, tableName, page) {
  $.ajax({
    url: '/api/sqlite/table-data',
    type: 'GET',
    data: {
      db_name: databaseId + '.db',
      table_name: tableName,
      page: page,
      page_size: recordsPerPage
    },
    dataType: 'json',
    success: function(data) {
      if (data) {
        tableData = data.data || [];
        totalRecords = data.total || 0;
        
        // 更新总数
        document.getElementById('total-records').textContent = totalRecords;
        document.getElementById('pagination-total').textContent = totalRecords;
        console.log("renderTableData:",tableData) 
        // 显示表格和分页
        renderTableData();
        renderPagination();
        document.getElementById('table-container').classList.remove('py-8');
        document.getElementById('pagination-container').classList.remove('d-none');
      } else {
        const errorMsg = data ? data.message : '获取数据失败';
        document.getElementById('table-container').innerHTML = `<div class="text-center py-8 text-danger"><i class="fa fa-exclamation-circle fa-3x mb-3"></i><p>${errorMsg}</p></div>`;
      }
    },
    error: function(xhr, status, error) {
      document.getElementById('table-container').innerHTML = `<div class="text-center py-8 text-danger"><i class="fa fa-exclamation-circle fa-3x mb-3"></i><p>获取数据出错: ${xhr.statusText}</p></div>`;
    }
  });
}

// 渲染表格数据
function renderTableData() {
  const tableContainer = document.getElementById('table-container');
  const startIndex = (currentPage - 1) * recordsPerPage;
  const endIndex = Math.min(startIndex + recordsPerPage, tableData.length);
  // 更新分页信息
  document.getElementById('start-record').textContent = tableData.length > 0 ? startIndex + 1 : 0;
  document.getElementById('end-record').textContent = endIndex;
  
  if (tableData.length === 0) {
    tableContainer.innerHTML = '<div class="text-center py-8 text-muted"><p>没有找到数据</p></div>';
    return;
  }
  
  // 创建表格
  let tableHTML = '<table class="db-table table table-striped table-bordered">';
  
  // 创建表头
  tableHTML += '<thead><tr>';
  const firstRow = tableData[0];
  Object.keys(firstRow).forEach(key => {
    // 将字段名转换为更友好的显示名称
    const displayName = key.split('_').map(word => word.charAt(0).toUpperCase() + word.slice(1)).join(' ');
    tableHTML += `<th>${displayName}</th>`;
  });
  tableHTML += '</tr></thead>';
  
  // 创建表体
  tableHTML += '<tbody>';
  tableData.forEach(row => {
    tableHTML += '<tr>';
    Object.values(row).forEach(value => {
      tableHTML += `<td>${value}</td>`;
    });
    tableHTML += '</tr>';
  });
  tableHTML += '</tbody>';
  tableHTML += '</table>';
  tableContainer.innerHTML = tableHTML;
}

// 渲染分页控件
function renderPagination() {
  const pageNumbersContainer = document.getElementById('page-numbers');
  const totalPages = Math.ceil(totalRecords / recordsPerPage);
  
  // 清空现有页码
  pageNumbersContainer.innerHTML = '';
  
  // 显示最多5个页码
  let startPage = Math.max(1, currentPage - 2);
  let endPage = Math.min(totalPages, startPage + 4);
  
  if (endPage - startPage < 4) {
    startPage = Math.max(1, endPage - 4);
  }
  
  // 添加首页
  if (startPage > 1) {
    addPageButton(1);
    if (startPage > 2) {
      addEllipsis();
    }
  }
  
  // 添加页码按钮
  for (let i = startPage; i <= endPage; i++) {
    addPageButton(i);
  }
  
  // 添加末页
  if (endPage < totalPages) {
    if (endPage < totalPages - 1) {
      addEllipsis();
    }
    addPageButton(totalPages);
  }
  
  // 更新上一页/下一页按钮状态
  document.getElementById('prev-page').querySelector('button').disabled = currentPage === 1;
  document.getElementById('next-page').querySelector('button').disabled = currentPage === totalPages;
  
  // 添加页码按钮
  function addPageButton(pageNum) {
    const li = document.createElement('li');
    li.className = `page-item ${currentPage === pageNum ? 'active' : ''}`;
    li.innerHTML = `<button class="page-link" onclick="goToPage(${pageNum})"></button>`;
    li.querySelector('button').textContent = pageNum;
    pageNumbersContainer.appendChild(li);
  }
  
  // 添加省略号
  function addEllipsis() {
    const li = document.createElement('li');
    li.className = 'page-item disabled';
    li.innerHTML = '<span class="page-link">...</span>';
    pageNumbersContainer.appendChild(li);
  }
}

// 跳转到指定页面
function goToPage(pageNum) {
  const totalPages = Math.ceil(totalRecords / recordsPerPage);
  if (pageNum >= 1 && pageNum <= totalPages) {
    currentPage = pageNum;
    
    // 显示加载状态
    const tableContainer = document.getElementById('table-container');
    tableContainer.innerHTML = '<div class="text-center py-8"><i class="fa fa-spinner fa-spin fa-3x text-muted mb-3"></i><p class="text-muted">加载数据中...</p></div>';
    document.getElementById('pagination-container').classList.add('d-none');
    
    // 调用API获取指定页的数据
    fetchTableData(currentDatabaseName, currentTableName, currentPage);
  }
}

// 初始化SQLite功能
function initSQLite() {
  // 前一页按钮事件
  document.getElementById('prev-page').querySelector('button').addEventListener('click', function() {
    if (currentPage > 1) {
      goToPage(currentPage - 1);
    }
  });

  // 后一页按钮事件
  document.getElementById('next-page').querySelector('button').addEventListener('click', function() {
    const totalPages = Math.ceil(totalRecords / recordsPerPage);
    if (currentPage < totalPages) {
      goToPage(currentPage + 1);
    }
  });

  // 加载所有数据库
  loadAllDatabases();

  // 绑定操作按钮事件（表管理）
  $('#btn-create-table').on('click', () => {
    const db = currentDatabaseName ? `${currentDatabaseName}.db` : prompt('输入数据库文件名（比如 demo.db）');
    if (!db) return;
    const table = prompt('输入新表名');
    if (!table) return;
    const colsStr = prompt('定义列（例如：id INTEGER PRIMARY KEY,name TEXT NOT NULL）');
    if (!colsStr) return;
    const columns = colsStr.split(',').map(s => {
      const [name, ...rest] = s.trim().split(/\s+/);
      const data_type = rest.join(' ') || 'TEXT';
      return { name, data_type, not_null: /NOT\s+NULL/i.test(data_type), primary_key: /PRIMARY\s+KEY/i.test(data_type) };
    });
    $.ajax({ url:'/api/sqlite/table/create', type:'POST', contentType:'application/json', data: JSON.stringify({ db_name: db, table_name: table, columns }),
      success: () => { alert('表创建成功'); loadTables(db.replace('.db',''), document.getElementById(`${db.replace('.db','')}-tables`)); },
      error: xhr => alert('创建失败: '+xhr.responseText)
    });
  });

  $('#btn-drop-table').on('click', () => {
    const db = currentDatabaseName ? `${currentDatabaseName}.db` : prompt('输入数据库文件名');
    const table = currentTableName || prompt('输入要删除的表名');
    if (!db || !table) return;
    if (!confirm(`确认删除表 ${table} (库: ${db})?`)) return;
    $.ajax({ url:'/api/sqlite/table/delete', type:'POST', contentType:'application/json', data: JSON.stringify({ db_name: db, table_name: table }),
      success: () => { alert('删除成功'); loadTables(db.replace('.db',''), document.getElementById(`${db.replace('.db','')}-tables`)); },
      error: xhr => alert('删除失败: '+xhr.responseText)
    });
  });

  $('#btn-rename-table').on('click', () => {
    const db = currentDatabaseName ? `${currentDatabaseName}.db` : prompt('输入数据库文件名');
    const table = currentTableName || prompt('输入当前表名');
    const newName = prompt('输入新表名');
    if (!db || !table || !newName) return;
    $.ajax({ url:'/api/sqlite/table/rename', type:'POST', contentType:'application/json', data: JSON.stringify({ db_name: db, table_name: table, new_name: newName }),
      success: () => { alert('重命名成功'); loadTables(db.replace('.db',''), document.getElementById(`${db.replace('.db','')}-tables`)); },
      error: xhr => alert('重命名失败: '+xhr.responseText)
    });
  });

  // 列管理
  $('#btn-rename-column').on('click', () => {
    const db = `${currentDatabaseName}.db`; if (!currentTableName) return alert('请先选择表');
    const oldName = prompt('输入旧列名');
    const newName = prompt('输入新列名');
    if (!oldName || !newName) return;
    $.ajax({ url:'/api/sqlite/column/rename', type:'POST', contentType:'application/json', data: JSON.stringify({ db_name: db, table_name: currentTableName, old_name: oldName, new_name: newName }),
      success: () => { alert('列重命名成功'); fetchTableData(currentDatabaseName, currentTableName, currentPage); },
      error: xhr => alert('列重命名失败: '+xhr.responseText)
    });
  });

  $('#btn-add-column').on('click', () => {
    const db = `${currentDatabaseName}.db`; if (!currentTableName) return alert('请先选择表');
    const name = prompt('输入新列名'); if (!name) return;
    const type = prompt('输入列类型（例如：TEXT, INTEGER, REAL）', 'TEXT') || 'TEXT';
    const notNull = confirm('是否 NOT NULL? (确定=是/取消=否)');
    const defStr = prompt('默认值（可留空）');
    const defVal = defStr ? parseLiteral(defStr) : null;
    $.ajax({ url:'/api/sqlite/column/add', type:'POST', contentType:'application/json', data: JSON.stringify({ db_name: db, table_name: currentTableName, name, data_type: type, not_null: notNull, default: defVal }),
      success: () => { alert('新增列成功'); fetchTableData(currentDatabaseName, currentTableName, currentPage); },
      error: xhr => alert('新增列失败: '+xhr.responseText)
    });
  });

  $('#btn-drop-column').on('click', () => {
    const db = `${currentDatabaseName}.db`; if (!currentTableName) return alert('请先选择表');
    const name = prompt('输入要删除的列名'); if (!name) return;
    if (!confirm(`确认删除列 ${name}?`)) return;
    $.ajax({ url:'/api/sqlite/column/drop', type:'POST', contentType:'application/json', data: JSON.stringify({ db_name: db, table_name: currentTableName, column_name: name }),
      success: () => { alert('删除列成功'); fetchTableData(currentDatabaseName, currentTableName, currentPage); },
      error: xhr => alert('删除列失败: '+xhr.responseText)
    });
  });

  // 数据操作
  $('#btn-insert-row').on('click', () => {
    const db = `${currentDatabaseName}.db`; if (!currentTableName) return alert('请先选择表');
    const kv = prompt('输入键值对（例如：name=张三,age=18）'); if (!kv) return;
    const values = parseKvPairs(kv);
    $.ajax({ url:'/api/sqlite/row/insert', type:'POST', contentType:'application/json', data: JSON.stringify({ db_name: db, table_name: currentTableName, values }),
      success: () => { alert('新增数据成功'); fetchTableData(currentDatabaseName, currentTableName, currentPage); },
      error: xhr => alert('新增数据失败: '+xhr.responseText)
    });
  });

  $('#btn-update-row').on('click', () => {
    const db = `${currentDatabaseName}.db`; if (!currentTableName) return alert('请先选择表');
    const pkCol = prompt('主键列名（或唯一列名）'); if (!pkCol) return;
    const pkValStr = prompt('主键值'); if (!pkValStr) return;
    const kv = prompt('要更新的键值对（例如：name=李四,age=28）'); if (!kv) return;
    const values = parseKvPairs(kv);
    $.ajax({ url:'/api/sqlite/row/update', type:'POST', contentType:'application/json', data: JSON.stringify({ db_name: db, table_name: currentTableName, pk_column: pkCol, pk_value: parseLiteral(pkValStr), values }),
      success: () => { alert('更新成功'); fetchTableData(currentDatabaseName, currentTableName, currentPage); },
      error: xhr => alert('更新失败: '+xhr.responseText)
    });
  });

  $('#btn-batch-delete').on('click', () => {
    const db = `${currentDatabaseName}.db`; if (!currentTableName) return alert('请先选择表');
    const pkCol = prompt('主键列名'); if (!pkCol) return;
    const listStr = prompt('主键值列表（逗号分隔）'); if (!listStr) return;
    const pk_values = listStr.split(',').map(s => parseLiteral(s.trim())).filter(v => v !== null);
    $.ajax({ url:'/api/sqlite/row/batch-delete', type:'POST', contentType:'application/json', data: JSON.stringify({ db_name: db, table_name: currentTableName, pk_column: pkCol, pk_values }),
      success: () => { alert('批量删除成功'); fetchTableData(currentDatabaseName, currentTableName, currentPage); },
      error: xhr => alert('批量删除失败: '+xhr.responseText)
    });
  });

  // SQL控制台
  $('#sql-run-btn').on('click', () => {
    const db = currentDatabaseName ? `${currentDatabaseName}.db` : prompt('输入数据库文件名'); if (!db) return;
    const sql = $('#sql-console-input').val(); if (!sql) return;
    $('#sql-console-results').html('<div class="text-muted"><i class="fa fa-spinner fa-spin"></i> 执行中...</div>');
    $.ajax({ url:'/api/sqlite/query', type:'POST', contentType:'application/json', data: JSON.stringify({ db_name: db, sql }),
      success: res => {
        if (res && res.data && Array.isArray(res.data)) {
          $('#sql-console-results').html(renderResultTable(res.data));
        } else {
          $('#sql-console-results').html(`<div class="alert alert-success p-2">执行成功，影响行数：${res.changed ?? 0}</div>`);
        }
      },
      error: xhr => $('#sql-console-results').html(`<div class="alert alert-danger p-2">执行失败：${xhr.responseText}</div>`)
    });
  });
}

// 导出初始化函数
window.initSQLite = initSQLite;

// 导出其他必要函数，使其在全局可用
window.toggleDatabase = toggleDatabase;
window.loadTableData = loadTableData;

// ---- 辅助函数 ----
function parseKvPairs(input) {
  const obj = {};
  input.split(',').forEach(pair => {
    const [k, v] = pair.split('=');
    if (!k) return;
    obj[k.trim()] = parseLiteral((v||'').trim());
  });
  return obj;
}

function parseLiteral(text) {
  if (text === '' || text.toLowerCase() === 'null') return null;
  if (/^\d+$/.test(text)) return Number(text);
  if (/^\d+\.\d+$/.test(text)) return Number(text);
  if (/^(true|false)$/i.test(text)) return text.toLowerCase() === 'true';
  return text; // as string
}

function renderResultTable(rows) {
  if (!rows || rows.length === 0) return '<div class="text-muted">无结果</div>';
  const cols = Object.keys(rows[0]);
  let html = '<table class="table table-sm table-bordered"><thead><tr>' + cols.map(c=>`<th>${c}</th>`).join('') + '</tr></thead><tbody>';
  rows.forEach(r => { html += '<tr>' + cols.map(c=>`<td>${r[c]}</td>`).join('') + '</tr>'; });
  html += '</tbody></table>';
  return html;
}